import random
import openpyxl
#2333333333


def generate_the_list() :

    data = openpyxl.Workbook()
    data = openpyxl.load_workbook("Data.xlsx")
    datasheet1 = data['Sheet1']

    name = {}
    sex = {}
    loop1 = 1
    while loop1 <= 68 :
        pairA = 'A' + str(loop1)
        pairB = 'B' + str(loop1)
        name[str(datasheet1[pairA].value)] = str(datasheet1[pairB].value)

        loop1 = loop1 + 1
    loop2 = 1
    while loop2 <= 68 :
        pairS = 'C' + str(loop2)
        pairA = 'A' + str(loop2)
        sex[str(datasheet1[pairA].value)] = str(datasheet1[pairS].value)
        loop2 = loop2 + 1
    print(name)
    print(sex)
    #从excel文件中读取序号，姓名，性别
    ranList = []
    abc = 0
    jump = False
    while abc <= 34 :

        while 1 :
       
            if jump == True :
                break

            else :
                value1 = random.randint(1,68)
                value2 = random.randint(1,68)
                #生成随机数
                if  ranList.count(value1) == False :
                    if ranList.count(value2) == False :
                #判断是否重复
                        if value1 != value2 :
                            #判断二值是否相同
                            if sex.get(str(value1)) == sex.get(str(value2)) :
                                ranList.append(value1)
                                ranList.append(value2)
                                break
                            #判断性别，扔出数据
                            if abc == 33 :
                                break

        if abc == 33 :
            jump = True
            value3 = random.randint(1,68)
            value4 = random.randint(1,68)
            if value3 != value4 :
                if ranList.count(value3) == False :
                    if ranlist.count(value4) == False :
                        ranList.append(value3)
                        ranList.append(value4)
                        abc = abc + 1
                
        if abc == 34 :
            while 1 :
                value5 = random.randint(1,68)
                value6 = random.randint(1,68)
                if value5 != value6 :
                    if ranList.count(value5) == False :
                        if ranList.count(value6) == False :
                            ranList.append(value5)
                            ranList.append(value6)
                            break
        #特殊处理
        print(ranList)
        abc = abc + 1

    print (len(ranList))

    transformcount0 = 0
    sight = 0
    newpair = []
    while transformcount0 <= 33 :
        sight = transformcount0 * 2
        newpair.append(str(name.get(str(ranList[sight])))+','+str(name.get(str(ranList[sight+1]))))
        transformcount0 = transformcount0 + 1

    anotherrandList = []
    anothercount = 0
    anothervalue = 0
    while anothercount <= 33 :
        while True :
            anothervalue =  random.randint(0,33)
            if anotherrandList.count(anothervalue) == 0 :
                anotherrandList.append(anothervalue)
                break
        anothercount = anothercount + 1

    aSum = []
    aSumcount = 0
    while aSumcount <= 67 :
        if aSumcount % 2 == 0 :
            aSumin = int(aSumcount / 2)
            add = str(newpair[anotherrandList[aSumin]])
            addS1 = add[0 : add.find(',')]
            addS2 = add[add.find(',') + 1 : len(add)]
            aSum.append(addS1)
            aSum.append(addS2)
        aSumcount = aSumcount + 1



    global wb
    wb = openpyxl.Workbook()
    firstSheet = wb.create_sheet()
    #新建工作簿
    sum = 0
    count = 1
    while count <= 8 :
        line = ord('A')
        while line <= ord('A') + 7 :
            coor = str(chr(line)) + str(count)
            firstSheet[coor] = aSum[sum]
            line = line + 1
            sum = sum + 1
        count = count + 1    
    #输出数据

    firstSheet['A9'] = aSum[-4]
    firstSheet['B9'] = aSum[-3]
    firstSheet['G9'] = aSum[-2]
    firstSheet['H9'] = aSum[-1]


    transformcount1 = 0
    sight1 = 0
    newpair1 = []
    while transformcount1 <= 33 :
        sight1 = transformcount1 * 2
        newpair1.append(str(aSum[sight1])+','+str(str(aSum[sight1 + 1])))
        transformcount1 = transformcount1 + 1
    print(newpair1)


ifpass = False 
while ifpass == False :
    generate_the_list()
    print('已经生成表格，重新生成（Y/N）？')
    getin = input()
    if getin == 'N' or getin == 'n' :
        ifpass = True
        wb.save("233.xlsx")
    elif getin == 'Y' or getin == 'y' :
        ifpass = False
    elif getin != 'Y' and getin != 'Y' and getin != 'N' and getin != 'n' :
        break



